﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using GoodMan.Infrastructure.Entities.Entity;
using GoodMan.Infrastructure.Entities;
using System.Data.SqlClient;
using System.Data;
using System.Data.OleDb;

namespace GoodMan.Portal.Biz
{
    public class ProductBiz
    {
        #region 后台管理
        public static bool SaveProduct(Product product, Policy policy, List<ProductRepertory> productRepertoryList, List<ProductRepertoryHistory> productRepertoryHistoryList)
        {
            try
            {

                if (product.Id > 0)
                {
                    var productTemp = new Product().Find(product.Id.ToString());
                    productTemp.ProductCode = product.ProductCode;
                    productTemp.ProductName = product.ProductName;
                    productTemp.Images = product.Images;
                    productTemp.Version = product.Version;
                    productTemp.PurchasePrice = product.PurchasePrice;
                    productTemp.ProductSubType = product.ProductSubType;
                    productTemp.ProductType = product.ProductType;
                    productTemp.CreateDate = DateTime.Now;
                    productTemp.Description = product.Description;
                    productTemp.OriginalPrice = product.OriginalPrice;
                    productTemp.CapsPeople = product.CapsPeople;
                    product = productTemp.PostModify();
                    if (product != null)
                    {
                        if (PolicyBiz.SavePolicy(policy) != null)
                        {
                            return true;
                        }
                        else
                        {
                            return false;
                        }
                    }
                    else
                    {
                        return false;
                    }


                }
                else
                {
                    //插入产品信息
                    product = product.PostAdd();
                    if (product != null)
                    {
                        //插入价格策略
                        policy.ProductId = product.Id;
                        policy = PolicyBiz.SavePolicy(policy);
                        if (productRepertoryList != null && productRepertoryList.Count != 0)
                        {
                            //插入库存记录
                            ProductRepertoryBiz.SaveproductRepertory(productRepertoryList, product.Id);

                        }
                        if (productRepertoryHistoryList != null && productRepertoryHistoryList.Count != 0)
                        {
                            //插入产品历史信息
                            ProductRepertoryBiz.SaveproductRepertoryHistory(productRepertoryHistoryList, product.Id);
                        }
                        return true;
                    }
                    else
                    {
                        return false;
                    }

                }
            }
            catch
            {
                return false;
            }
        }


        public static Product GetProductById(int productID)
        {
            return new Product().Find(productID.ToString());
        }

        public static ItemListBox<Product> GetAllProductsByType(string productkey, int areaCode, int categoryId, int subcategoryId, int page, int pagesize)
        {
            var count = new Product().Count(c =>c.AreaCode==areaCode&& (c.ProductType == categoryId || categoryId == 0) && (c.ProductSubType == subcategoryId || subcategoryId == 0) && (c.ProductName.Contains(productkey) || c.ProductCode.Contains(productkey)));
            using (Context ctx = new Context())
            {
                string sql = @"select top (@pagesize) t.* from (select ROW_NUMBER() OVER (ORDER BY a.createdate desc) AS RowNumber,a.*,b.remaincount,d.Price,case when e.Price is null then d.Price else e.price end as policyprice,f.CategoryName,g.CategoryName as SubCategoryName,
                    case when h.id is null then '否' else '是' end as recomendedstr from T_Product a
                    inner join (select ProductId,SUM(Remain) as remaincount from dbo.T_ProductRepertory group by ProductId) b
                    on a.Id=b.ProductId
                    inner join (select * from dbo.T_Policy c where c.PolicyType=1) d 
                    on a.Id=d.ProductId
                    left join (select top 1 * from dbo.T_Policy c where c.IsActive=1 and c.PolicyType=2 and GETDATE() between c.StartDate and c.ExpireDate) e 
                    on a.Id=e.ProductId
                    left join dbo.T_ProductCategory f on a.ProductType=f.Id
                    left join dbo.T_ProductCategory g on a.ProductSubType=g.Id
                    left join dbo.T_recommended h on a.Id=h.productid
                    where a.AreaCode=@areaCode and (a.ProductType=@categoryId or @categoryId=0) and (a.ProductSubType=@subcategoryId or @subcategoryId=0) and (a.ProductName like '%'+@productkey+'%' or a.ProductCode like '%'+@productkey+'%')) t
                    where t.RowNumber>@pagesize*@page";

                var datatable = ctx.ExcuteSQL(sql,
                    new SqlParameter("@categoryId", categoryId),
                     new SqlParameter("@subcategoryId", subcategoryId),
                     new SqlParameter("@productkey", productkey),
                      new SqlParameter("@areaCode", areaCode),
                      new SqlParameter("@pagesize", pagesize),
                     new SqlParameter("@page", page)
                    );

                List<Product> data = null;
                if (datatable != null && datatable.Rows.Count > 0)
                {
                    data = new List<Product>();
                    foreach (DataRow row in datatable.Rows)
                    {
                        var product = new Product()
                        {
                            CreateDate = Convert.ToDateTime(row["CreateDate"].ToString()),
                            Id = int.Parse(row["Id"].ToString()),
                            Images = (row["Images"].ToString() != null&&row["Images"].ToString() !="") ? row["Images"].ToString().Split(';')[0] : "",
                            ProductCode = row["ProductCode"].ToString(),
                            ProductName = row["ProductName"].ToString(),
                            PurchasePrice = decimal.Parse(row["PurchasePrice"] != null ? row["PurchasePrice"].ToString() : "0"),
                            TotalRemain = int.Parse(row["remaincount"] != null ? row["remaincount"].ToString() : "0"),
                            DefaulSalePrice = decimal.Parse(row["Price"] != null ? row["Price"].ToString() : "0"),
                            PolicyPrice = decimal.Parse(row["policyprice"] != null ? row["policyprice"].ToString() : row["Price"].ToString()),
                            CategoryName = row["CategoryName"].ToString(),
                            SubCategoryName = row["SubCategoryName"].ToString(),
                            Recomendedstr = row["recomendedstr"].ToString()
                        };
                        product.Integral = Convert.ToInt32(Math.Round(product.DefaulSalePrice * SupportBiz.GetCostRank(), 0));
                        data.Add(product);
                    }
                }
                var list = new ItemListBox<Product>(data).BuildPage(
                    count, page, pagesize, new PageParameter() { Style = "workspace" });
                return list;
            }

        }
        /// <summary>
        /// 快餐的查询，适用于查询产品表和策略表以及商户表
        /// </summary>
        /// <param name="productkey"></param>
        /// <param name="areaCode"></param>
        /// <param name="page"></param>
        /// <param name="pageSize"></param>
        /// <param name="businessId"></param>
        /// <param name="style"></param>
        /// <returns></returns>
        public static ItemListBox<Product> GetAllProducts(string productkey, int areaCode, int page, int pageSize, int businessId = -1, string style = "workspace")
        {
            using (Context ctx = new Context())
            {
                var query = from c in ctx.Product
                            join p in ctx.Policy
                            on c.Id equals p.ProductId
                            join b in ctx.Business
                            on c.BusinessId equals b.Id
                            select new {c,p,b};
                query = query.Where(c => c.c.AreaCode == 10);
                if (!string.IsNullOrEmpty(productkey))
                {
                    query = query.Where(c => c.c.ProductName.Contains(productkey) || c.c.ProductCode.Contains(productkey));
                }
                if (businessId != -1) {
                    query = query.Where(c => c.b.Id == businessId);
                }
                var data = query.OrderByDescending(c => c.c.CreateDate).Skip(page * pageSize).Take(pageSize).ToList().Select(c => new Product().Set(c.c).LoadPolicy(c.p).Loadbusiness(c.b)).ToList();
                var itemCount = query.Count();
                return new ItemListBox<Product>(data).BuildPage(itemCount, page, pageSize, new PageParameter()
                {
                    Style = style
                });
            }
        }

        /// <summary>
        /// 套餐的查询，适用于查询产品表和策略表
        /// </summary>
        /// <param name="productkey"></param>
        /// <param name="areaCode"></param>
        /// <param name="page"></param>
        /// <param name="pageSize"></param>
        /// <param name="style"></param>
        /// <returns></returns>
        public static ItemListBox<Product> GetAllProductsByPackages(string productkey, int areaCode, int page, int pagesize, string style = "workspace")
        {
            var count = new Product().Count(c => (c.ProductName.Contains(productkey) || c.ProductCode.Contains(productkey)) && c.AreaCode == 4);
            using (Context ctx = new Context())
            {
                string sql = @"select top (@pagesize) t.* from (select ROW_NUMBER() OVER (ORDER BY a.createdate desc) AS RowNumber,a.*,b.remaincount,d.Price,case when h.id is null then '否' else '是' end as recomendedstr
                    from T_Product a
                    inner join (select ProductId,SUM(Remain) as remaincount from dbo.T_ProductRepertory group by ProductId) b
                    on a.Id=b.ProductId
                    inner join (select * from dbo.T_Policy c where c.PolicyType=1) d 
                    on a.Id=d.ProductId
                    left join dbo.T_recommended h on a.Id=h.productid
                    where a.AreaCode=@areaCode and (a.ProductName like '%'+@productkey+'%' or a.ProductCode like '%'+@productkey+'%')) t
                    where t.RowNumber>@pagesize*@page";

                var datatable = ctx.ExcuteSQL(sql,
                     new SqlParameter("@productkey", productkey),
                      new SqlParameter("@areaCode", areaCode),
                      new SqlParameter("@pagesize", pagesize),
                     new SqlParameter("@page", page)
                    );

                List<Product> data = null;
                if (datatable != null && datatable.Rows.Count > 0)
                {
                    data = new List<Product>();
                    foreach (DataRow row in datatable.Rows)
                    {
                        var product = new Product()
                        {
                            CreateDate = Convert.ToDateTime(row["CreateDate"].ToString()),
                            Id = int.Parse(row["Id"].ToString()),
                            Images = (row["Images"].ToString() != null && row["Images"].ToString() != "") ? row["Images"].ToString().Split(';')[0] : "",
                            ProductCode = row["ProductCode"].ToString(),
                            ProductName = row["ProductName"].ToString(),
                            PurchasePrice = decimal.Parse(row["PurchasePrice"] != null ? row["PurchasePrice"].ToString() : "0"),
                            TotalRemain = int.Parse(row["remaincount"] != null ? row["remaincount"].ToString() : "0"),
                            DefaulSalePrice = decimal.Parse(row["Price"] != null ? row["Price"].ToString() : "0"),
                            Version = row["Version"].ToString(),
                            Recomendedstr = row["recomendedstr"].ToString()
                        };
                        product.Integral = Convert.ToInt32(Math.Round(product.DefaulSalePrice * SupportBiz.GetCostRank(), 0));
                        data.Add(product);
                    }
                }
                var list = new ItemListBox<Product>(data).BuildPage(
                    count, page, pagesize, new PageParameter() { Style = style });
                return list;
            }

        }


        public static bool DelProduct(int productID)
        {
            try
            {
                var product = new Product().Find(productID.ToString());
                if (product != null)
                {
                    product.PostDelete();
                    PolicyBiz.DelPolicy(productID);
                    ProductRepertoryBiz.DelProductRepertory(productID);
                    //如果有推荐和特卖也要删除
                    SpecialSaleBiz.DelSpecialSale(productID);
                    RecommendedBiz.DelRecommended(productID);
                }
                return true;
            }
            catch (Exception ex)
            {
                //日志记录
                return false;
            }
            return false;
        }

        public static Product GetProductByPoductCode(string productCode)
        {
            using (Context ctx = new Context())
            {
                string sql = @"select a.*,b.remaincount,d.Price,
                    case when e.Price is null then d.Price else e.price end as policyprice
                     from T_Product a
                    inner join (select ProductId,SUM(Remain) as remaincount from dbo.T_ProductRepertory group by ProductId) b
                    on a.Id=b.ProductId
                    inner join (select * from dbo.T_Policy c where c.PolicyType=1) d 
                    on a.Id=d.ProductId
                    left join (select top 1 * from dbo.T_Policy c where c.IsActive=1 and c.PolicyType=2 and GETDATE() between c.StartDate and c.ExpireDate) e 
                    on a.Id=e.ProductId
                    where a.ProductCode=@productCode and a.AreaCode=1";

                var datatable = ctx.ExcuteSQL(sql,
                    new SqlParameter("@productCode", productCode)
                    );

                Product product = null;
                if (datatable != null && datatable.Rows.Count > 0)
                { 
                    foreach (DataRow row in datatable.Rows)
                    {
                         product = new Product()
                        {
                            CreateDate = Convert.ToDateTime(row["CreateDate"].ToString()),
                            Id = int.Parse(row["Id"].ToString()),
                            Images = (row["Images"].ToString() != null && row["Images"].ToString() != "") ? row["Images"].ToString().Split(';')[0] : "",
                            ProductCode = row["ProductCode"].ToString(),
                            ProductName = row["ProductName"].ToString(),
                            PurchasePrice = decimal.Parse(row["PurchasePrice"] != null ? row["PurchasePrice"].ToString() : "0"),
                            TotalRemain = int.Parse(row["remaincount"] != null ? row["remaincount"].ToString() : "0"),
                            DefaulSalePrice = decimal.Parse(row["Price"] != null ? row["Price"].ToString() : "0"),
                            PolicyPrice = decimal.Parse(row["policyprice"] != null ? row["policyprice"].ToString() : row["Price"].ToString())
                            
                        };
                        //product.Integral = Convert.ToInt32(Math.Round(product.DefaulSalePrice * SupportBiz.GetCostRank(), 0));
                        
                    }
                }
                return product;
            }
        }

        #endregion

        #region 前台操作
        public static Product GetProductByIdForView(int productId, int areaCode = 1)
        {
            using (Context ctx = new Context())
            {
                //套餐
                if (areaCode == 4)
                {
                    var data = (from c in ctx.Product
                                join f in ctx.Policy
                                on c.Id equals f.ProductId
                                where c.Id == productId && f.PolicyType == 1
                                select new { c, f })
                                   .ToList()
                                   .Select(e => new Product().Set(e.c).LoadPolicy(e.f))
                                   .FirstOrDefault();

                    return data;
                }
                else
                {
                    var data = (from c in ctx.Product
                                join di in ctx.ProductCategory
                                 on c.ProductType equals di.Id into dt
                                from d in dt.DefaultIfEmpty()
                                join f in ctx.Policy
                                on c.Id equals f.ProductId
                                where c.Id == productId && f.PolicyType == 1
                                select new { c, d, f })
                               .ToList()
                               .Select(e => new Product().Set(e.c).LoadProductCategory(e.d).LoadPolicy(e.f))
                               .FirstOrDefault();

                    return data;
                }
            }
        }

        public static ItemListBox<Product> GetProductListByAreaCode(int areaCode = 1, int type = 1, int page = 0, int pagesize = 28)
        {
            //areaCode1：普通商品 2：团购商品 3：积分兑换商品 4：商城套餐区 10：快餐
            var count = 0;
            if (areaCode == 1)//普通商品区
            {
                if (type == 1)//普通商品
                {
                    #region 普通商品
                    using (Context ctx = new Context())
                    {
                        string sqlcount = @"select ROW_NUMBER() OVER (ORDER BY a.id desc) AS RowNumber, a.*,d.Price from T_Product a
                         inner join (select * from dbo.T_Policy c where c.PolicyType=1) d on a.Id=d.ProductId
                         where a.AreaCode=1
                         and a.Id not in (select ProductId from T_Policy where IsActive=1 and PolicyType=2 and GETDATE() between StartDate and ExpireDate)";
                        var datatable = ctx.ExcuteSQL(sqlcount);
                        count = datatable != null ? datatable.Rows.Count : 0;
                    }
                    using (Context ctx = new Context())
                    {
                        string sql = @"select top (@pagesize) t.* from (select ROW_NUMBER() OVER (ORDER BY a.id desc) AS RowNumber, a.*,d.Price from T_Product a
                         inner join (select * from dbo.T_Policy c where c.PolicyType=1) d on a.Id=d.ProductId
                         where a.AreaCode=1
                         and a.Id not in (select ProductId from T_Policy where IsActive=1 and PolicyType=2 and GETDATE() between StartDate and ExpireDate)) t
                            where t.RowNumber>@pagesize*@page";

                        var datatable = ctx.ExcuteSQL(sql,
                             new SqlParameter("@pagesize", pagesize),
                                new SqlParameter("@page", page)
                            );
                        List<Product> data = null;
                        if (datatable != null && datatable.Rows.Count > 0)
                        {
                            data = new List<Product>();
                            foreach (DataRow row in datatable.Rows)
                            {
                                var product = new Product()
                                {
                                    CreateDate = Convert.ToDateTime(row["CreateDate"].ToString()),
                                    Id = int.Parse(row["Id"].ToString()),
                                    Images = (row["Images"].ToString() != null&&row["Images"].ToString() !="") ? row["Images"].ToString().Split(';')[0] : "",
                                    ProductCode = row["ProductCode"].ToString(),
                                    ProductName = row["ProductName"].ToString(),
                                    PurchasePrice = decimal.Parse(row["PurchasePrice"] != null ? row["PurchasePrice"].ToString() : "0"),
                                    DefaulSalePrice = decimal.Parse(row["Price"] != null ? row["Price"].ToString() : "0")
                                };
                                //product.Integral = Convert.ToInt32(Math.Round(product.DefaulSalePrice * 100, 0));
                                data.Add(product);
                            }
                        }
                        var list = new ItemListBox<Product>(data).BuildPage(
                            count, page, pagesize, new PageParameter() { Style = "home" });
                        return list;
                    }


                    #endregion
                }
                else if (type == 3)//限时特卖
                {
                    #region 限时特卖
                    var date = DateTime.Now;
                    using (Context ctx = new Context())
                    {
                        string sqlcount = @"select a.*,c.Price from T_Product a
                                inner join (select * from t_policy b where b.IsActive=1 and b.PolicyType=2 and GETDATE() between b.StartDate and b.ExpireDate) c 
                                on a.Id=c.ProductId
                                where a.AreaCode=1";
                        var datatable = ctx.ExcuteSQL(sqlcount);
                        count = datatable != null ? datatable.Rows.Count : 0;
                    }
                    using (Context ctx = new Context())
                    {
                        string sql = @"select top (@pagesize) t.* from (select ROW_NUMBER() OVER (ORDER BY a.id desc) AS RowNumber, a.*,d.Price,c.Price as policyprice from T_Product a
                            inner join (select * from dbo.T_Policy c where c.PolicyType=1) d on a.Id=d.ProductId
                            inner join (select top 1 * from t_policy b where b.IsActive=1 and b.PolicyType=2 and GETDATE() between b.StartDate and b.ExpireDate) c 
                            on a.Id=c.ProductId
                            where a.AreaCode=1) t
                            where t.RowNumber>@pagesize*@page";

                        var datatable = ctx.ExcuteSQL(sql,
                             new SqlParameter("@pagesize", pagesize),
                                new SqlParameter("@page", page)
                            );
                        List<Product> data = null;
                        if (datatable != null && datatable.Rows.Count > 0)
                        {
                            data = new List<Product>();
                            foreach (DataRow row in datatable.Rows)
                            {
                                var product = new Product()
                                {
                                    CreateDate = Convert.ToDateTime(row["CreateDate"].ToString()),
                                    Id = int.Parse(row["Id"].ToString()),
                                    Images = (row["Images"].ToString() != null&&row["Images"].ToString() !="") ? row["Images"].ToString().Split(';')[0] : "",
                                    ProductCode = row["ProductCode"].ToString(),
                                    ProductName = row["ProductName"].ToString(),
                                    PurchasePrice = decimal.Parse(row["PurchasePrice"] != null ? row["PurchasePrice"].ToString() : "0"),
                                    DefaulSalePrice = decimal.Parse(row["Price"] != null ? row["Price"].ToString() : "0"),
                                    PolicyPrice = decimal.Parse(row["policyprice"] != null ? row["policyprice"].ToString() : "0.00")
                                };
                                //product.Integral = Convert.ToInt32(Math.Round(product.DefaulSalePrice * 100, 0));
                                data.Add(product);
                            }
                        }
                        var list = new ItemListBox<Product>(data).BuildPage(
                            count, page, pagesize, new PageParameter() { Style = "home" });
                        return list;
                    }
                    #endregion
                }
                else if (type == 2)//特别推荐
                {
                    #region 特别推荐
                    count = new Recommended().Count(c => 1 == 1);
                    using (Context ctx = new Context())
                    {
                        string sql = @"select top (@pagesize) t.* from (select ROW_NUMBER() OVER (ORDER BY a.id desc) AS RowNumber, a.*,d.Price,c.Price as policyprice from T_Product a
                             inner join T_Recommended e on a.Id=e.ProductId                             
                            inner join (select * from dbo.T_Policy c where c.PolicyType=1) d on a.Id=d.ProductId
                            left join (select top 1 * from t_policy b where b.IsActive=1 and b.PolicyType=2 and GETDATE() between b.StartDate and b.ExpireDate) c 
                            on a.Id=c.ProductId
                            where a.AreaCode=1) t
                            where t.RowNumber>@pagesize*@page";

                        var datatable = ctx.ExcuteSQL(sql,
                             new SqlParameter("@pagesize", pagesize),
                                new SqlParameter("@page", page)
                            );
                        List<Product> data = null;
                        if (datatable != null && datatable.Rows.Count > 0)
                        {
                            data = new List<Product>();
                            foreach (DataRow row in datatable.Rows)
                            {
                                var product = new Product()
                                {
                                    CreateDate = Convert.ToDateTime(row["CreateDate"].ToString()),
                                    Id = int.Parse(row["Id"].ToString()),
                                    Images = (row["Images"].ToString() != null&&row["Images"].ToString() !="") ? row["Images"].ToString().Split(';')[0] : "",
                                    ProductCode = row["ProductCode"].ToString(),
                                    ProductName = row["ProductName"].ToString(),
                                    PurchasePrice = decimal.Parse(row["PurchasePrice"] != null ? row["PurchasePrice"].ToString() : "0"),
                                    DefaulSalePrice = decimal.Parse(row["Price"] != null ? row["Price"].ToString() : "0"),
                                    PolicyPrice = decimal.Parse(row["policyprice"] != null ? row["policyprice"].ToString()!=""?row["policyprice"].ToString():"0.00" : "0.00")
                                };
                                //product.Integral = Convert.ToInt32(Math.Round(product.DefaulSalePrice * 100, 0));
                                data.Add(product);
                            }
                        }
                        var list = new ItemListBox<Product>(data).BuildPage(
                           count, page, pagesize, new PageParameter() { Style = "home" });
                        return list;
                    }
                    #endregion
                }
            }
            else//2：团购商品 3：积分兑换商品
            {
                #region 2：团购商品 3：积分兑换商品
                
                
                count = new Product().Count(c => c.AreaCode == areaCode);
                using(Context ctx =new Context())
                {
                    string sql = @"select top (@pagesize) t.* from (select ROW_NUMBER() OVER (ORDER BY a.id desc) AS RowNumber, a.*,d.Price from T_Product a
                         inner join (select * from dbo.T_Policy c where c.PolicyType=1) d on a.Id=d.ProductId
                         where a.AreaCode=@areacode) t
                            where t.RowNumber>@pagesize*@page";

                    var datatable = ctx.ExcuteSQL(sql,
                         new SqlParameter("@pagesize", pagesize),
                          new SqlParameter("@areacode", areaCode),
                            new SqlParameter("@page", page)
                        );
                    List<Product> data = null;
                    if (datatable != null && datatable.Rows.Count > 0)
                    {
                        data = new List<Product>();
                        foreach (DataRow row in datatable.Rows)
                        {
                            var product = new Product()
                            {
                                CreateDate = Convert.ToDateTime(row["CreateDate"].ToString()),
                                Id = int.Parse(row["Id"].ToString()),
                                Images = (row["Images"].ToString() != null&&row["Images"].ToString() !="") ? row["Images"].ToString().Split(';')[0] : "",
                                ProductCode = row["ProductCode"].ToString(),
                                ProductName = row["ProductName"].ToString(),
                                PurchasePrice = decimal.Parse(row["PurchasePrice"] != null ? row["PurchasePrice"].ToString() : "0"),
                                DefaulSalePrice = decimal.Parse(row["Price"] != null ? row["Price"].ToString() : "0"),
                                OriginalPrice = decimal.Parse(row["OriginalPrice"] != null ? row["OriginalPrice"].ToString() : "0")
                            };
                            if (areaCode == 2)
                            {
                                product.SaleCount = OrderBiz.GetProductSaleCount(product.Id);
                            }
                            product.Integral = Convert.ToInt32(Math.Round(product.DefaulSalePrice * SupportBiz.GetCostRank(), 0));
                            data.Add(product);
                        }
                    }
                    var list = new ItemListBox<Product>(data).BuildPage(
                        count, page, pagesize, new PageParameter() { Style = "home" });
                    return list;
                }
                #endregion
            }
            return null;
        }

        public static ItemListBox<Product> GetProductListByCategory(int category, int subcategory,int page = 0, int pagesize = 28)
        {
            int count = 0;
            using (Context ctx = new Context())
            {
                string sqlcount = @"select ROW_NUMBER() OVER (ORDER BY a.id desc) AS RowNumber, a.*,d.Price from T_Product a
                         inner join (select * from dbo.T_Policy c where c.PolicyType=1) d on a.Id=d.ProductId
                         where a.AreaCode=1 and (a.ProductType=@productType or @productType=0) and (a.ProductSubType=@productSubType or @productSubType=0)
                         and a.Id not in (select ProductId from T_Policy where IsActive=1 and PolicyType=2 and GETDATE() between StartDate and ExpireDate)";
                var datatable = ctx.ExcuteSQL(sqlcount,
                    new SqlParameter("@productType", category),
                    new SqlParameter("@productSubType", subcategory)
                );
                count = datatable != null ? datatable.Rows.Count : 0;
            }
            using (Context ctx = new Context())
            {
                string sql = @"select top (@pagesize) t.* from (select ROW_NUMBER() OVER (ORDER BY a.id desc) AS RowNumber, a.*,d.Price from T_Product a
                         inner join (select * from dbo.T_Policy c where c.PolicyType=1) d on a.Id=d.ProductId
                         where a.AreaCode=1 and (a.ProductType=@productType or @productType=0) and (a.ProductSubType=@productSubType or @productSubType=0)
                         and a.Id not in (select ProductId from T_Policy where IsActive=1 and PolicyType=2 and GETDATE() between StartDate and ExpireDate)) t
                            where t.RowNumber>@pagesize*@page";

                var datatable = ctx.ExcuteSQL(sql,
                    new SqlParameter("@productType", category),
                    new SqlParameter("@productSubType", subcategory),
                     new SqlParameter("@pagesize", pagesize),
                        new SqlParameter("@page", page)
                    );
                List<Product> data = null;
                if (datatable != null && datatable.Rows.Count > 0)
                {
                    data = new List<Product>();
                    foreach (DataRow row in datatable.Rows)
                    {
                        var product = new Product()
                        {
                            CreateDate = Convert.ToDateTime(row["CreateDate"].ToString()),
                            Id = int.Parse(row["Id"].ToString()),
                            Images = (row["Images"].ToString() != null&&row["Images"].ToString() !="") ? row["Images"].ToString().Split(';')[0] : "",
                            ProductCode = row["ProductCode"].ToString(),
                            ProductName = row["ProductName"].ToString(),
                            PurchasePrice = decimal.Parse(row["PurchasePrice"] != null ? row["PurchasePrice"].ToString() : "0"),
                            DefaulSalePrice = decimal.Parse(row["Price"] != null ? row["Price"].ToString() : "0")
                        };
                        //product.Integral = Convert.ToInt32(Math.Round(product.DefaulSalePrice * 100, 0));
                        data.Add(product);
                    }
                }
                var list = new ItemListBox<Product>(data).BuildPage(
                    count, page, pagesize, new PageParameter() { Style = "home" });
                return list;
            }
        }

        
        #endregion

        public static int ProductImport(string xlsfile)
        {
            int totle = 0;
            //源的定义
            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + xlsfile + ";" + "Extended Properties='Excel 8.0;HDR=NO;IMEX=1';";
            //Sql语句
            string strExcel = "select * from [sheet1$]";
            //定义存放的数据表
            DataSet ds = new DataSet();
            //连接数据源
            OleDbConnection conn = new OleDbConnection(strConn);
            conn.Open();
            //适配到数据源
            OleDbDataAdapter adapter = new OleDbDataAdapter(strExcel, strConn);
            adapter.Fill(ds);
            conn.Close();

            var o = ds.Tables[0];
            foreach (DataRow row in o.Rows)
            {
                object code = row[0];
                object num = row[1];
                if (code != null && num != null)
                {
                    string pcode = code.ToString();
                    int pnum = 0;
                    if (Int32.TryParse(num.ToString(), out pnum))
                    {
                        using (Context ctx = new Context())
                        {
                            var item = (from c in ctx.Product
                                        join r in ctx.ProductRepertory
                                         on c.Id equals r.ProductId
                                        where c.ProductCode == pcode
                                        select r).FirstOrDefault();
                            if (item != null)
                            {
                                var productRepertory = new ProductRepertory().Set(item);
                                productRepertory.Remain += pnum;
                                if (productRepertory.PostModify() != null)
                                {
                                    totle += pnum;
                                }
                            }
                        }
                    }
                }
            }
            return totle;
        }
        /// <summary>
        /// 获取产品库存
        /// </summary>
        /// <param name="productId"></param>
        /// <param name="dormitoryId">不查楼栋库存时，请不要设置此值</param>
        /// <returns></returns>
        public static int GetProductRemain(int productId, int dormitoryId = 0)
        {
            var data = iCache.Get<List<ProductRepertory>>(String.Format("ProductBiz.GetProductRemain_{0}",
                productId), () =>
                {
                    List<ProductRepertory> list = new ProductRepertory().FindAll(c => c.ProductId == productId);
                    return list;
                }, 60);
            if (data != null)
            {
                if (dormitoryId > 0)
                {
                    return data.Where(c => c.DormitoryId == dormitoryId).Sum(c=>c.Remain);
                }

                return data.Sum(c => c.Remain);
            }
            return 0;
        }
        /// <summary>
        /// 刷新产品库存缓存
        /// </summary>
        /// <param name="productId"></param>
        public static void RefreshProductRemain(int productId)
        {
            iCache.Expire(String.Format("ProductBiz.GetProductRemain_{0}",
                productId));
        }

        public static List<Product> GetFiveRecommended()
        {
            using (Context ctx = new Context())
            {
                string sql = @"select top 5 t.* from (select ROW_NUMBER() OVER (ORDER BY a.id desc) AS RowNumber, a.*,d.Price,c.Price as policyprice from T_Product a
                             inner join T_Recommended e on a.Id=e.ProductId                             
                            inner join (select * from dbo.T_Policy c where c.PolicyType=1) d on a.Id=d.ProductId
                            left join (select top 1 * from t_policy b where b.IsActive=1 and b.PolicyType=2 and GETDATE() between b.StartDate and b.ExpireDate) c 
                            on a.Id=c.ProductId
                            where a.AreaCode=1) t";

                var datatable = ctx.ExcuteSQL(sql);
                List<Product> data = null;
                if (datatable != null && datatable.Rows.Count > 0)
                {
                    data = new List<Product>();
                    foreach (DataRow row in datatable.Rows)
                    {
                        var product = new Product()
                        {
                            CreateDate = Convert.ToDateTime(row["CreateDate"].ToString()),
                            Id = int.Parse(row["Id"].ToString()),
                            Images = (row["Images"].ToString() != null&&row["Images"].ToString() !="") ? row["Images"].ToString().Split(';')[0] : "",
                            ProductCode = row["ProductCode"].ToString(),
                            ProductName = row["ProductName"].ToString(),
                            PurchasePrice = decimal.Parse(row["PurchasePrice"] != null ? row["PurchasePrice"].ToString() : "0"),
                            DefaulSalePrice = decimal.Parse(row["Price"] != null ? row["Price"].ToString() : "0"),
                            PolicyPrice = decimal.Parse(row["policyprice"] != null ? row["policyprice"].ToString() != "" ? row["policyprice"].ToString() : "0.00" : "0.00")
                        };
                        //product.Integral = Convert.ToInt32(Math.Round(product.DefaulSalePrice * 100, 0));
                        data.Add(product);
                    }
                }
                 
                return data;
            }
        }

        public static List<Product> GetFiveHot()
        {
            using (Context ctx = new Context())
            {
                string sql = @"select top 5 a.*,c.Price,b.salecount from T_Product a
inner join (select * from T_Policy where PolicyType=1) c on a.Id=c.ProductId
left join (select top 5 d.ProductId,d.salecount from (select  ProductId,sum(Num) as salecount from dbo.T_OrderDetail group by ProductId) d order by d.salecount desc) b on a.Id=b.ProductId
where a.AreaCode=1 order by b.salecount desc,a.createdate desc";

                var datatable = ctx.ExcuteSQL(sql);
                List<Product> data = null;
                if (datatable != null && datatable.Rows.Count > 0)
                {
                    data = new List<Product>();
                    foreach (DataRow row in datatable.Rows)
                    {
                        var product = new Product();
                        
                            product.CreateDate = Convert.ToDateTime(row["CreateDate"].ToString());
                            product.Id = int.Parse(row["Id"].ToString());
                            product.Images = (row["Images"].ToString() != null&&row["Images"].ToString() !="") ? row["Images"].ToString().Split(';')[0] : "";
                            product.ProductCode = row["ProductCode"].ToString();
                            product.ProductName = row["ProductName"].ToString();
                           product.PurchasePrice = decimal.Parse(row["PurchasePrice"] != null ? row["PurchasePrice"].ToString() : "0");
                            product.DefaulSalePrice = decimal.Parse(row["Price"] != null ? row["Price"].ToString() : "0");
                            product.SaleCount = int.Parse((row["salecount"] != null && row["salecount"].ToString() != "") ? row["salecount"].ToString() : "0");
                       
                        //product.Integral = Convert.ToInt32(Math.Round(product.DefaulSalePrice * 100, 0));
                        data.Add(product);
                    }
                }

                return data;
            }
        }

        public static List<Product> GetFiveSepecial()
        {
            List<Product> productlist = null;
            using (Context ctx = new Context())
            {
                string sql = @"select top 4 t.* from (select ROW_NUMBER() OVER (ORDER BY a.id desc) AS RowNumber, a.*,d.Price,c.Price as policyprice from T_Product a
                            inner join (select * from dbo.T_Policy c where c.PolicyType=1) d on a.Id=d.ProductId
                            inner join (select top 1 * from t_policy b where b.IsActive=1 and b.PolicyType=2 and GETDATE() between b.StartDate and b.ExpireDate) c 
                            on a.Id=c.ProductId
                            where a.AreaCode=1) t";

                var datatable = ctx.ExcuteSQL(sql);

                if (datatable != null && datatable.Rows.Count > 0)
                {
                    productlist = new List<Product>();
                    foreach (DataRow row in datatable.Rows)
                    {
                        var product = new Product()
                        {
                            CreateDate = Convert.ToDateTime(row["CreateDate"].ToString()),
                            Id = int.Parse(row["Id"].ToString()),
                            Images = (row["Images"].ToString() != null&&row["Images"].ToString() !="") ? row["Images"].ToString().Split(';')[0] : "",
                            ProductCode = row["ProductCode"].ToString(),
                            ProductName = row["ProductName"].ToString(),
                            PurchasePrice = decimal.Parse(row["PurchasePrice"] != null ? row["PurchasePrice"].ToString() : "0"),
                            DefaulSalePrice = decimal.Parse(row["Price"] != null ? row["Price"].ToString() : "0"),
                            PolicyPrice = decimal.Parse(row["policyprice"] != null ? row["policyprice"].ToString() : "0.00")
                        };
                        //product.Integral = Convert.ToInt32(Math.Round(product.DefaulSalePrice * 100, 0));
                        productlist.Add(product);
                    }
                }
            }
            return productlist;
        }

        public static List<Product> GetFiveGroupBuy()
        {
             List<Product> data = null;
            using (Context ctx = new Context())
            {
                string sql = @"select top 5 t.* from (select ROW_NUMBER() OVER (ORDER BY a.id desc) AS RowNumber, a.*,d.Price from T_Product a
                         inner join (select * from dbo.T_Policy c where c.PolicyType=1) d on a.Id=d.ProductId
                         where a.AreaCode=2) t";

                var datatable = ctx.ExcuteSQL(sql);
               
                if (datatable != null && datatable.Rows.Count > 0)
                {
                    data = new List<Product>();
                    foreach (DataRow row in datatable.Rows)
                    {
                        var product = new Product()
                        {
                            CreateDate = Convert.ToDateTime(row["CreateDate"].ToString()),
                            Id = int.Parse(row["Id"].ToString()),
                            Images = (row["Images"].ToString() != null&&row["Images"].ToString() !="") ? row["Images"].ToString().Split(';')[0] : "",
                            ProductCode = row["ProductCode"].ToString(),
                            ProductName = row["ProductName"].ToString(),
                            PurchasePrice = decimal.Parse(row["PurchasePrice"] != null ? row["PurchasePrice"].ToString() : "0"),
                            DefaulSalePrice = decimal.Parse(row["Price"] != null ? row["Price"].ToString() : "0"),
                            OriginalPrice = decimal.Parse(row["OriginalPrice"] != null ? row["OriginalPrice"].ToString() : "0")
                        };
                         
                        //product.Integral = Convert.ToInt32(Math.Round(product.DefaulSalePrice * SupportBiz.GetCostRank(), 0));
                        data.Add(product);
                    }
                }
            }
            return data;
        }

        public static List<Product> GetFiveExchange()
        {
            List<Product> data = null;
            using (Context ctx = new Context())
            {
                string sql = @"select top 6 t.* from (select ROW_NUMBER() OVER (ORDER BY a.id desc) AS RowNumber, a.*,d.Price from T_Product a
                         inner join (select * from dbo.T_Policy c where c.PolicyType=1) d on a.Id=d.ProductId
                         where a.AreaCode=3) t";

                var datatable = ctx.ExcuteSQL(sql);

                if (datatable != null && datatable.Rows.Count > 0)
                {
                    data = new List<Product>();
                    foreach (DataRow row in datatable.Rows)
                    {
                        var product = new Product()
                        {
                            CreateDate = Convert.ToDateTime(row["CreateDate"].ToString()),
                            Id = int.Parse(row["Id"].ToString()),
                            Images = (row["Images"].ToString() != null&&row["Images"].ToString() !="") ? row["Images"].ToString().Split(';')[0] : "",
                            ProductCode = row["ProductCode"].ToString(),
                            ProductName = row["ProductName"].ToString(),
                            PurchasePrice = decimal.Parse(row["PurchasePrice"] != null ? row["PurchasePrice"].ToString() : "0"),
                            DefaulSalePrice = decimal.Parse(row["Price"] != null ? row["Price"].ToString() : "0"),
                            OriginalPrice = decimal.Parse(row["OriginalPrice"] != null ? row["OriginalPrice"].ToString() : "0")
                        };

                        product.Integral = Convert.ToInt32(Math.Round(product.DefaulSalePrice * SupportBiz.GetCostRank(), 0));
                        data.Add(product);
                    }
                }
            }
            return data;
        }

        public static List<Product> ProductAppraisie(int orderId)
        {
             using(Context ctx = new Context())
            {
                string sql = @"select c.* from dbo.T_OrderDetail a
                        inner join dbo.T_Product c on a.ProductId=c.Id
                        where a.OrderId=@orderId and a.ProductId not in
                        (select ProductId from dbo.T_Appraise b where  b.orderid=@orderId)";

                var datatable = ctx.ExcuteSQL(sql,
                   new SqlParameter("@orderId", orderId)
                   );
                List<Product> data = null;
                if (datatable != null && datatable.Rows.Count > 0)
                {
                    data = new List<Product>();
                    foreach (DataRow row in datatable.Rows)
                    {
                        var product = new Product()
                        { 
                            Id = int.Parse(row["Id"].ToString()),
                            ProductName = row["ProductName"].ToString()
                            
                        };
                        
                        data.Add(product);
                    }
                }
                return data;
            }
        }
    }
}
